package com.chao.yyks.modular.problem.service.impl;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.chao.common.util.DateUtile;
import com.chao.common.util.StrUtile;
import com.chao.common.util.XlsMsgBean;
import com.chao.common.util.XlsUtil;
import com.chao.common.util.exception.SysException;
import com.chao.yyks.modular.hearing.bean.EnHearing;
import com.chao.yyks.modular.hearing.mapper.EnHearingMapper;
import com.chao.yyks.modular.label.bean.EnLabel;
import com.chao.yyks.modular.label.mapper.EnLabelMapper;
import com.chao.yyks.modular.paragraph.bean.EnParagraph;
import com.chao.yyks.modular.paragraph.mapper.EnParagraphMapper;
import com.chao.yyks.modular.problem.bean.*;
import com.chao.yyks.modular.problem.mapper.*;
import com.chao.yyks.modular.problem.service.EnProblemImportService;
import com.chao.yyks.util.ReadDocxExample;
import com.chao.yyks.util.SpliteText;
import com.google.common.collect.ImmutableList;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.*;
import java.util.stream.Collectors;


/**
 * 问题答案ServiceImpl
 * chao
 * 2024-09-12
 */
@Service
public class EnProblemImportServiceImpl implements EnProblemImportService {

	/**
	 * 问题答案Mapper
	 */
	@Autowired
	private EnProblemAnswerMapper enProblemAnswerMapper;

	/**
	 * 题库Mapper
	 */
	@Autowired
	private EnProblemMapper enProblemMapper;

	/**
	 * 听力Mapper
	 */
	@Autowired
	private EnHearingMapper enHearingMapper;

	/**
	 * 阅读文章Mapper
	 */
	@Autowired
	private EnParagraphMapper enParagraphMapper;

	/**
	 * 词汇信息Mapper
	 */
	@Autowired
	private EnWordBankMapper enWordBankMapper;

	/**
	 * 板块Mapper
	 */
	@Autowired
	private EnLabelMapper enLabelMapper;

	/**
	 * 问题类型Mapper
	 */
	@Autowired
	private EnProblemTypeMapper enProblemTypeMapper;

	/**
	 * 问题答案Mapper
	 */
	@Autowired
	private EnProblemOptionMapper enProblemOptionMapper;

	/**
	 * word文档解析 转表格
	 */
	@Override
	public void wordToExl(HttpServletResponse response, MultipartFile file) {
		List<String> strs = ReadDocxExample.wordText(file);
		if("听力题".equals(strs.get(0))){
			main1(strs,file.getName(),response);
		}else {
			main2(strs,file.getName(),response);
		}
	}

//	public static void main(String[] args) {
//		String[] aa= {
//				"D:\\微信文件\\WeChat Files\\wxid_q5khawj4k5ek22\\FileStorage\\File\\2024-10\\社会科学六级听力2.docx"
////				,"E:\\第一周听力\\六级听力题1人文.docx"
////				,"E:\\bbb\\社会科学四级听力1.docx"
////				,"E:\\bbb\\社会科学阅读题1.docx"
////				,"E:\\bbb\\四级听力科技1.docx"
////				"E:\\阅读\\社会科学\\阅读题1.docx"
//		};
//		for (String f: aa) {
//			File file = new File(f);
//			List<String> strs = ReadDocxExample.wordText(file);
//			if ("听力题".equals(strs.get(0))) {
//				main1(strs, file.getName(),null);
//			} else {
//				main2(strs, file.getName(),null);
//			}
//		}
//	}

	public static void main2(List<String> strs,String fileName,HttpServletResponse response) {
		List<JSONObject> dataList = new ArrayList<>();
		String upType = null;
		String section = null;
		String label = null;
		String directions = null;
		String type = null;
		String report  = null;
		StringBuilder s = new StringBuilder(); // 文章缓存
		StringBuilder s1 = new StringBuilder(); // 文章缓存
		for (int i = 0; i < strs.size(); i++) {
			String str = strs.get(i);
			if(StrUtile.isEmpty(str)){
				continue;
			}
			if("人文科学".contains(str) || "科学技术".contains(str) || "社会科学".contains(str) || "文艺体育".contains(str)){
				label = str;
				continue;
			}
			if("阅读题".contains(str)){
				upType = "Reading Comprehension(阅读)";
				continue;
			}
			if("词汇题".contains(str)){
				upType = "word(词汇)";
				type = "词汇题";
				continue;
			}
			if(str.startsWith("事实题")){
				type = "事实题";
				continue;
			}
			if(str.startsWith("推断题")){
				type = "推断题";
				continue;
			}
			if(str.startsWith("Section")){
				if(str.contains("A") && !"词汇题".equals(type)){
					type = "填空题";
				}
				if(str.contains("B") && !"词汇题".equals(type)){
					type = "句子题";
				}
				section = str;
				continue;
			}
			if(str.startsWith("Directions")){
				directions = str;
				continue;
			}
			if(str.startsWith("原版") || str.startsWith("第")){
				report = "1"; // 开始文章
				continue;
			}
			if(str.startsWith("注释版")){
				report = "2"; // 开始译文
				continue;
			}
			// 问题开始
			if(str.startsWith("Problem:")){
				report = null;
				JSONObject json = new JSONObject();
				if(!StrUtile.isEmpty(s.toString())){
					json.put("report",s.toString().trim());
					s = new StringBuilder();
				}
				if(!StrUtile.isEmpty(s1.toString())){
					json.put("report1",s1.toString().trim());
					s1 = new StringBuilder();
				}
				json.put("upType",upType);
				upType = null;
				json.put("type",type);
				json.put("label",label);
				json.put("section",section);
				section = null;
				json.put("directions",directions);
				directions = null;
				json.put("problem",str.replace("Problem:","").replace(".","").trim());
				if("句子题".equals(type)) {
					i++;
					str = strs.get(i);
					if (str.contains("Answer:")) {
						json.put("answer", str.substring(str.indexOf("Answer:") + 7).trim());
					}
					String[] an = {"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T"};
					for (int j = 0; j < an.length; j++) {
						json.put("xz" + j, an[j]);
					}
					dataList.add(json);
				}else if("填空题".equals(type)) {
					List<JSONObject> js = new ArrayList<>();
					while (true) {
						i++;
						str = strs.get(i);
						if (str.contains("Answer:")) {
							json.put("answer", str.substring(str.indexOf("Answer:") + 7).trim());
						}
						if (str.contains("Explanation")) {
							json.put("explanation", str.trim());
							js.add(json);
							break;
						}
					}
					i++;
					while (true) {
						str = strs.get(i);
						if (str.contains("Word Bank")) {
							i++;
							List<String> ss = new ArrayList<>();
							while (true) {
								str = strs.get(i);
								if (!StrUtile.isEmpty(str)) {
									ss.add(str.substring(str.indexOf(".") + 1));
								}
								if (str.contains("O.")) {
									break;
								}
								i++;
							}
							for (JSONObject jsonObject : js) {
								for (int k = 0; k < ss.size(); k++) {
									jsonObject.put("xz" + k, ss.get(k).trim());
								}
							}
							dataList.addAll(js);
							break;
						}
						JSONObject da = new JSONObject();
						while (true) {
							str = strs.get(i);
							if(StrUtile.isEmpty(str)){
								break;
							}
							if (str.startsWith("Problem:")) {
								da.put("problem", str.replace("Problem:", "").replace(".", "").trim());
								da.put("type", type);
								da.put("label", label);
							}
							if (str.contains("Answer:")) {
								da.put("answer", str.substring(str.indexOf("Answer:") + 7).trim());
							}
							if (str.contains("Explanation")) {
								da.put("explanation", str.trim());
								js.add(da);
								break;
							}
							i++;
						}
						i++;
					}
				}else {
					i++;
					for (int j = i; j < strs.size(); j++, i++) {
						String str1 = strs.get(j);
						if (str1.contains("A.") || str1.contains("A)")) {
							json.put("xz0", str1.replace("A.", "").replace("A)", "").trim());
						}
						if (str1.contains("B.") || str1.contains("B)")) {
							json.put("xz1", str1.replace("B.", "").replace("B)", "").trim());
						}
						if (str1.contains("C.") || str1.contains("C)")) {
							json.put("xz2", str1.replace("C.", "").replace("C)", "").trim());
						}
						if (str1.contains("D.") || str1.contains("D)")) {
							json.put("xz3", str1.replace("D.", "").replace("D)", "").trim());
						}
						if (str1.contains("Answer:")) {
							json.put("answer", str1.substring(str1.indexOf("Answer:") + 7).trim());
							if("词汇题".equals(type)){
								break;
							}
						}
						if (str1.contains("Explanation")) {
							json.put("explanation", str1.trim());
							break;
						}
					}
					dataList.add(json);
				}

			}
			if("1".equals(report)){
				s.append(str).append("\n");
			}
			if("2".equals(report)){
				s1.append(str).append("\n");
			}
		}

		try {
			// 通过writeValueAsString方法 可以把 对象转json串、List转Json串、Map转json串（这个过程被称为序列化）
			String[] keys = {"upType", "section", "directions", "report", "report1", "label",
					"type", "problem", "answer", "explanation", "xz0", "xz1", "xz2", "xz3", "xz4", "xz5",
					"xz6", "xz7", "xz8", "xz9", "xz10", "xz11", "xz12", "xz13", "xz14", "xz15", "xz16", "xz17", "xz18", "xz19"};

			JSONObject hide = new JSONObject();
			hide.put("upType","题型");
			hide.put("section","Section");
			hide.put("directions","题目说明");
			hide.put("report","文章内容");
			hide.put("report1","文章翻译");
			hide.put("label","题目板块");
			hide.put("type","小分类");
			hide.put("problem","题目");
			hide.put("answer","答案");
			hide.put("explanation","答案解析");
			hide.put("xz0","选项A");
			hide.put("xz1","选项B");
			hide.put("xz2","选项C");
			hide.put("xz3","选项D");
			hide.put("xz4","选项E");
			hide.put("xz5","选项F");
			hide.put("xz6","选项G");
			hide.put("xz7","选项H");
			hide.put("xz8","选项I");
			hide.put("xz9","选项J");
			hide.put("xz10","选项K");
			hide.put("xz11","选项L");
			hide.put("xz12","选项M");
			hide.put("xz13","选项N");
			hide.put("xz14","选项O");
			hide.put("xz15","选项P");
			hide.put("xz16","选项Q");
			hide.put("xz17","选项R");
			hide.put("xz18","选项S");
			hide.put("xz19","选项T");

			dataList.add(0,hide);


			try {
				HSSFWorkbook workbook = XlsUtil.getXls(dataList, keys);
				if(null == response){
					// 写入文件
					try (FileOutputStream fileOut = new FileOutputStream("E:/"+label+fileName+".xls")) {
						workbook.write(fileOut);
					} catch (IOException e) {
						e.printStackTrace();
					} finally {
						// 关闭工作簿
						try {
							workbook.close();
						} catch (IOException e) {
							e.printStackTrace();
						}
					}
					return;
				}
				//准备将Excel的输出流通过response输出到页面下载
				//八进制输出流
				response.setContentType("application/octet-stream");
				//这后面可以设置导出Excel的名称，此例中名为student.xls
				response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(DateUtile.toymdhms() + strs.get(0)+".xls", "UTF-8"));
				//刷新缓冲
				response.flushBuffer();
				//写入输出流
				workbook.write(response.getOutputStream());
			} catch (IOException e) {
				e.printStackTrace();
			}

		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public static void main1(List<String> strs,String fileName,HttpServletResponse response) {
		List<JSONObject> dataList = new ArrayList<>();
		String upType = "Listening Comprehension(听力题)";
		String section = null;
		String announcer = null;
		String label = null;
		String directions = null;
		String type = null;
		String report  = null;
		String questions  = null;
		String conversation = null;
		StringBuilder s = new StringBuilder(); // 文章缓存
		List<String> ss = new ArrayList<>(); // 对话缓存
		for (int i = 1; i < strs.size(); i++) {
			String str = strs.get(i);
			if(StrUtile.isEmpty(str)){
				continue;
			}
			if("人文科学".contains(str) || "科学技术".contains(str) || "社会科学".contains(str) || "文艺体育".contains(str)){
				label = str;
				continue;
			}
			if("客服男声".contains(str) || "教育女声".contains(str) || "咨询女声".contains(str)){
				announcer = str;
				continue;
			}
			if(str.startsWith("事实题")){
				type = "事实题";
				continue;
			}
			if(str.startsWith("推断题")){
				type = "推断题";
				continue;
			}
			if(str.startsWith("Section")){
				if(!ss.isEmpty()){ // 如果对话没有结束
					for (String s1 : ss) {
						JSONObject json = new JSONObject();
						if (s1.contains(":")) {
							json.put("announcer", s1.split(":")[0].trim());
							json.put("report", s1.substring(s1.indexOf(":")+1).trim());
						} else {
							json.put("announcer", StrUtile.isEmpty(announcer) ? "教育女声" : announcer);
							json.put("report", s1);
						}
						dataList.add(json);
					}
					ss = new ArrayList<>();
				}
				section = str;
				continue;
			}
			if(str.startsWith("Directions")){
				directions = str;

				continue;
			}
			if(str.startsWith("News Report") || str.startsWith("Passage")){
				report = "1"; // 开始文章
				s.append(str).append("\n");
				continue;
			}
			if(str.startsWith("Recording")){
				report = "3"; // 开始文章
				conversation = "是";
				ss.add(announcer+":"+str);
				continue;
			}
			if(str.startsWith("Conversation")){
				if(!ss.isEmpty()){ // 如果对话没有结束
					for (String s1 : ss) {
						JSONObject json = new JSONObject();
						if (s1.contains(":")) {
							json.put("announcer", s1.split(":")[0].trim());
							json.put("report", s1.substring(s1.indexOf(":")+1).trim());
						} else {
							json.put("announcer", StrUtile.isEmpty(announcer) ? "教育女声" : announcer);
							json.put("report", s1);
						}
						dataList.add(json);
					}
					ss = new ArrayList<>();
				}
				conversation = "是";
				ss.add(str);
				report = "2"; // 开始对话
				continue;
			}
			if(str.startsWith("Questions")){
				questions = str;
				if("2".equals(report)){
					ss.add(str);
				}
				if("3".equals(report)){
					String s1 = ss.remove(ss.size() -1);
					ss.add(s1 + "\n" + str);
				}
				report = null; // 结束文章和对话
				continue;
			}
			if("1".equals(report)){
				s.append(str).append("\n");
				continue;
			}
			if("2".equals(report)){
				ss.add(str);
				continue;
			}
			if("3".equals(report)){
				String s1 = ss.remove(ss.size() -1);
				if(s1.contains(announcer)){
					s1 += "\n"+str;
					ss.add(s1);
				}else {
					ss.add(s1);
					ss.add(announcer+":"+str);
				}
				continue;
			}
			// 问题开始
			if(str.startsWith("Problem:")){
				JSONObject json = new JSONObject();
				json.put("hearingDialogue",conversation);
				conversation = null;
				if(!ss.isEmpty()){
					String s1 = ss.get(0);
					if(!StrUtile.isEmpty(directions)){
						json.put("report",directions.trim());
						json.put("announcer",StrUtile.isEmpty(announcer)?"教育女声":announcer.trim());
						announcer = null;
					}else {
						if (s1.contains(":")) {
							json.put("announcer", s1.split(":")[0].trim());
							json.put("report", s1.substring(s1.indexOf(":")+1).trim());
						} else {
							json.put("announcer", StrUtile.isEmpty(announcer) ? "教育女声" : announcer.trim());
							json.put("report", s1.trim());
						}
						ss.remove(0);
					}
				}
				if(!StrUtile.isEmpty(s.toString())){
					json.put("hearingDialogue","否");
					json.put("announcer",StrUtile.isEmpty(announcer)?"教育女声":announcer.trim());
					if(!StrUtile.isEmpty(directions)){
						json.put("report",directions.trim() +"\n"+ s.toString().trim() +"                         \n"+questions.trim());
					}else {
						json.put("report",s.toString().trim() +"                         \n" +questions.trim());
					}
					s = new StringBuilder();
					announcer = null;
				}
				json.put("upType",upType);
				upType = null;
				json.put("type",type);
				json.put("label",label);
				json.put("section",section);
				section = null;
				json.put("directions",directions);
				directions = null;
				json.put("problem",str.replace("Problem:","").trim());
				i++;
				for (int j = i; j < strs.size(); j++,i++) {
					String str1 = strs.get(j);
					if(str1.contains("A.") || str1.contains("A)")){
						json.put("A",str1.replace("A.","").replace("A)","").trim());
					}
					if(str1.contains("B.") || str1.contains("B)")){
						json.put("B",str1.replace("B.","").replace("B)","").trim());
					}
					if(str1.contains("C.") || str1.contains("C)")){
						json.put("C",str1.replace("C.","").replace("C)","").trim());
					}
					if(str1.contains("D.") || str1.contains("D)")){
						json.put("D",str1.replace("D.","").replace("D)","").trim());
					}
					if(str1.contains("Answer:")){
						json.put("answer",str1.substring(str1.indexOf("Answer:")+7).trim());
						break;
					}
				}
				dataList.add(json);
			}
		}

		try {
			// 通过writeValueAsString方法 可以把 对象转json串、List转Json串、Map转json串（这个过程被称为序列化）
			String[] keys = {"upType", "section", "directions", "hearingDialogue", "announcer", "report", "label",
					"type", "problem", "answer", "A", "B", "C", "D"};

			JSONObject hide = new JSONObject();
			hide.put("upType","题型");
			hide.put("section","Section");
			hide.put("directions","题目说明");
			hide.put("hearingDialogue","是否对话");
			hide.put("announcer","语音");
			hide.put("report","听力文字");
			hide.put("label","题目板块");
			hide.put("type","小分类");
			hide.put("problem","题目");
			hide.put("answer","答案");
			hide.put("A","选项A");
			hide.put("B","选项B");
			hide.put("C","选项C");
			hide.put("D","选项D");
			dataList.add(0,hide);


			try {
				HSSFWorkbook workbook = XlsUtil.getXls(dataList, keys);
				if(null == response){
					// 写入文件
					try (FileOutputStream fileOut = new FileOutputStream("E:/"+label+fileName+".xls")) {
						workbook.write(fileOut);
					} catch (IOException e) {
						e.printStackTrace();
					} finally {
						// 关闭工作簿
						try {
							workbook.close();
						} catch (IOException e) {
							e.printStackTrace();
						}
					}
					return;
				}
				//准备将Excel的输出流通过response输出到页面下载
				//八进制输出流
				response.setContentType("application/octet-stream");
				//这后面可以设置导出Excel的名称，此例中名为student.xls
				response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(DateUtile.toymdhms() +"听力题.xls", "UTF-8"));
				//刷新缓冲
				response.flushBuffer();
				//写入输出流
				workbook.write(response.getOutputStream());
			} catch (IOException e) {
				e.printStackTrace();
			}

		} catch (Exception e) {
			e.printStackTrace();
		}
	}


	@Override
	@Transactional
	public JSONObject importEnProblemTed(MultipartFile file) {
		JSONArray msg = new JSONArray();
		//解析附件
		JSONArray sheetArray = XlsUtil.getXlsJson(file);
//		JSONArray sheetArray = XlsUtil.getXlsJson(new File("E:\\aaa.xlsx"));
		if (sheetArray.isEmpty()) {
			throw new SysException("读取文件发生错误:表格为空");
		}

		// 答案排序
		String[] an = {"A","B","C","D"};
		// 缓存主键 文章标识
		String hcId = "";
		//听力文章列表
		List<EnHearing> hearingList = new ArrayList<>();
		//试题列表
		List<EnProblem> problemList = new ArrayList<>();
		//试题答案列表
		List<EnProblemAnswer> problemAnswerList = new ArrayList<>();
		// 试题答案
		List<EnProblemOption> optionList = new ArrayList<>();

		// 获取第一个工作簿 是普通题目
		sheetArray = sheetArray.getJSONArray(0);

		String section = null;
		for (int i = 1; i < sheetArray.size(); i++) {
			JSONArray rowArray = sheetArray.getJSONArray(i);
			// Section
			String key1 = XlsUtil.getValue(rowArray, 1);
			if(!StrUtile.isEmpty(key1)){
				section = key1;
			}
			// 题目说明
			String key2 = XlsUtil.getValue(rowArray, 2);
			// 阅读文章/听力文字
			String key3 = XlsUtil.getValue(rowArray, 3);
			// 答案解析
			String key9 = XlsUtil.getValue(rowArray, 5);
			// 题目板块
			String key5 = XlsUtil.getValue(rowArray, 6);
			// 小分类
			String key6 = XlsUtil.getValue(rowArray, 7);
			// 题干
			String key7 = XlsUtil.getValue(rowArray, 8);
			// 答案
			String key8 = XlsUtil.getValue(rowArray, 9);


			if(!StrUtile.isEmpty(key2) && !StrUtile.isEmpty(key3)){
				EnHearing enHearing = new EnHearing();
				hcId = StrUtile.getId();
				enHearing.setHearingId(hcId);
				enHearing.setUpHearingId(StrUtile.getId());
				enHearing.setHearingSection(section);
				enHearing.setHearingDirections(key2);
				enHearing.setHearingTitleTs(key3);
				enHearing.setCtime(new Date());
				enHearing.setHearingType("2");
				hearingList.add(enHearing);
			}
			if(StrUtile.isEmpty(key7) || StrUtile.isEmpty(key5)){
				continue;
			}

			EnProblem problem = new EnProblem();
			problem.setProblemId(StrUtile.getId());
			problem.setProblemTitle(key7);
			problem.setHearingId(hcId);
			if(!StrUtile.isEmpty(key9)){
				EnProblemAnswer problemAnswer = new EnProblemAnswer();
				problemAnswer.setProblemAnswerLong(key9);
				problemAnswer.setProblemId(problem.getProblemId());
				problemAnswerList.add(problemAnswer);
			}
			// 选项
			int s = 0;
			for (int j = 10;j<rowArray.size() || j< 50;j++){
				String key = XlsUtil.getValue(rowArray, j);
				if(StrUtile.isEmpty(key)){
					break;
				}
				EnProblemOption option = new EnProblemOption();
				option.setOptionId(StrUtile.getId());
				option.setOptionTitle(key);
				option.setOptionTrue("2");
				option.setProblemId(problem.getProblemId());
				if(an[j-10].equals(key8)){
					problem.setProblemAnswer(key);
					option.setOptionTrue("1");
				}
				optionList.add(option);
				s++;
			}
			if (StrUtile.isEmpty(problem.getProblemAnswer())) {
				msg.add(new XlsMsgBean("试题页第" + (i + 1) + "行第J列", "问题答案解析失败!"));
			}
			if (s < 4) {
				msg.add(new XlsMsgBean("试题页第" + (i + 1) + "行", "问题候选项少于4个,请检查!"));
			}
			problemList.add(problem);
		}
		if(msg.isEmpty()){
			if(!problemList.isEmpty()){
				enProblemMapper.insertAllEnProblem(problemList,null);
			}
			if(!optionList.isEmpty()){
				enProblemOptionMapper.insertAllEnProblemOption(optionList);
			}
			if(!problemAnswerList.isEmpty()){
				enProblemAnswerMapper.insertAllEnProblemAnswer(problemAnswerList);
			}
			if(!hearingList.isEmpty()){
				enHearingMapper.insertAllEnHearing(hearingList);
			}
		}
		JSONObject rut = new JSONObject();
		rut.put("list", msg);
		return rut;
	}


	@Override
	@Transactional
	public JSONObject importEnProblem(MultipartFile file,String problemDate) {
		if(StrUtile.isEmpty(problemDate)){
			throw new SysException("请选择试题日期!");
		}
		JSONArray msg = new JSONArray();
		//解析附件
		JSONArray sheetArray = XlsUtil.getXlsJson(file);
//		JSONArray sheetArray = XlsUtil.getXlsJson(new File("C:\\Users\\PC\\Documents\\WeChat Files\\wxid_q5khawj4k5ek22\\FileStorage\\File\\2024-11\\第六周翻译写作.xlsx"));
		if (sheetArray.isEmpty()) {
			throw new SysException("读取文件发生错误:表格为空");
		}
		List<EnLabel> labelList = enLabelMapper.selectEnLabelByList(new JSONObject()).getList();
		Map<String,String> labelMap = labelList.stream().collect(Collectors.toMap(EnLabel::getLabelName, EnLabel::getLabelId));

		List<EnProblemType> typeList = enProblemTypeMapper.selectEnProblemTypeByList(new JSONObject()).getList();
		Map<String,String> typeMap = typeList.stream().collect(Collectors.toMap(t->t.getProblemUpType()+t.getProblemName(), EnProblemType::getProblemType));

		Map<String,String> upT = new HashMap<String,String>(){{
			put("Reading Comprehension(阅读)","2");
			put("word(词汇)","3");
			put("Speaking(口语)","4");
			put("Translation(翻译)","5");
			put("Writing(写作题)","6");
		}};

//		String userId = RequestUtile.getLoginUserId();
		// 答案排序
		String[] an = {"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T"};
		// 缓存字段 题型 Section 题目说明 阅读文章/听力文字 文章译文 题目板块
		String[] hc = {"","","","","","","","",""};
		// 缓存主键 文章标识
		String hcId = "";
		//文章列表
		List<EnParagraph> paragraphList = new ArrayList<>();
		//听力文章列表
		List<EnHearing> hearingList = new ArrayList<>();
		//试题列表
		List<EnProblem> problemList = new ArrayList<>();
		//试题答案列表
		List<EnProblemAnswer> problemAnswerList = new ArrayList<>();
		// 试题答案
		List<EnProblemOption> optionList = new ArrayList<>();
		// 词汇生词
		List<EnWordBank> wordBankList = new ArrayList<>();

		if(sheetArray.size() > 1){ // 第二个sheet页是听力4级
			Hearing6(sheetArray.getJSONArray(1),"1",labelMap,typeMap,hearingList,problemList,optionList,msg,problemDate);
		}
		if(sheetArray.size() > 2){ // 第三个sheet页是听力6级
			Hearing6(sheetArray.getJSONArray(2),"2",labelMap,typeMap,hearingList,problemList,optionList,msg,problemDate);
		}
		// 获取第一个工作簿 是普通题目
		sheetArray = sheetArray.getJSONArray(0);

		for (int i = 1; i < sheetArray.size(); i++) {
			JSONArray rowArray = sheetArray.getJSONArray(i);
			// 题型
			String key0 = XlsUtil.getValue(rowArray, 0);
			if(!StrUtile.isEmpty(key0)){
				hc = new String[]{"", "", "", "", "", "", "", "", ""};
				hc[0] = key0;
			}
			// Section
			String key1 = XlsUtil.getValue(rowArray, 1);
			if(!StrUtile.isEmpty(key1)){
				hc[1] = key1;
			}
			// 题目说明
			String key2 = XlsUtil.getValue(rowArray, 2);
			if(!StrUtile.isEmpty(key2)){
				hc[2] = key2;
			}
			// 阅读文章/听力文字
			String key3 = XlsUtil.getValue(rowArray, 3);
			if(!StrUtile.isEmpty(key3)){
				hc[3] = key3;
			}
			// 文章译文
			String key4 = XlsUtil.getValue(rowArray, 4);
			if(!StrUtile.isEmpty(key4)){
				hc[4] = key4;
			}
			if(!upT.containsKey(hc[0])){
				msg.add(new XlsMsgBean("试题页第" + (i + 1) + "行第A列", "题型错误,无法解析!"));
				continue;
			}
			// 题目板块
			String key5 = XlsUtil.getValue(rowArray, 5);
			// 小分类
			String key6 = XlsUtil.getValue(rowArray, 6);
			// 题干
			String key7 = XlsUtil.getValue(rowArray, 7);
			// 答案
			String key8 = XlsUtil.getValue(rowArray, 8);
			// 答案解析
			String key9 = XlsUtil.getValue(rowArray, 9);

			if(!labelMap.containsKey(key5)){
				msg.add(new XlsMsgBean("试题页第" + (i + 1) + "行第F列", "题目所属板块填写错误!"));
				continue;
			}

			String upType = upT.get(hc[0]);
			// 阅读题
			if("2".equals(upType)){
				// 如果是新开始的 Section 则重新建立一篇文章
				if(!StrUtile.isEmpty(key1) || !StrUtile.isEmpty(key2) || !StrUtile.isEmpty(key3)){
					EnParagraph enParagraph = new EnParagraph();
					hcId = StrUtile.getId();
					enParagraph.setParagraphId(hcId);
					enParagraph.setParagraphSection(hc[1]);
					enParagraph.setParagraphDirections(hc[2]);
					enParagraph.setParagraphTitle(hc[3]);
					enParagraph.setParagraphTitleTs(hc[4]);
					enParagraph.setProblemUpType("2");
					enParagraph.setProblemDate(problemDate);
					enParagraph.setCtime(new Date());
					enParagraph.setLabelId(labelMap.get(key5));
					paragraphList.add(enParagraph);
				}
			}else if("3".equals(upType) && !StrUtile.isEmpty(key7)){ // 词汇题
				hcId = null;
				if(!StrUtile.isEmpty(hc[1]) && !paragraphList.isEmpty()){
					for (EnParagraph paragraph:paragraphList){
						if(hc[1].equals(paragraph.getParagraphSection()) && labelMap.getOrDefault(key5,"aaa").equals(paragraph.getLabelId())){
							hcId = paragraph.getParagraphId();
							break;
						}
					}
				}
				if(StrUtile.isEmpty(hcId)){
					msg.add(new XlsMsgBean("试题页第" + (i + 1) + "行", "词汇题未找到文章!"));
				}
				EnWordBank wordBank = new EnWordBank();
				wordBank.setWordBank(key7);
				wordBank.setWordBankTs(key4);
				wordBankList.add(wordBank);
			}else if("4".equals(upType)){ // 口语题
				// 如果是新开始的做题要求,则认为是一篇新的文章
				if(!StrUtile.isEmpty(key2) || !StrUtile.isEmpty(key3)){
					EnHearing enHearing = new EnHearing();
					hcId = StrUtile.getId();
					enHearing.setHearingId(hcId);
					enHearing.setUpHearingId(StrUtile.getId());
					enHearing.setHearingDirections(hc[2]);
					if("复述题".equals(key6)){
						enHearing.setHearingTitleTs(key3);
					}else {
						enHearing.setHearingTitle(key3);
						enHearing.setAnnouncer("教育女声");
					}
					enHearing.setProblemUpType(upType);
					enHearing.setCtime(new Date());
					enHearing.setProblemDate(problemDate);
					enHearing.setHearingType("1");
					enHearing.setLabelId(labelMap.get(key5));
					hearingList.add(enHearing);
				}
				if(StrUtile.isEmpty(key7)){
					EnProblem problem = new EnProblem();
					problem.setProblemId(StrUtile.getId());
					problem.setProblemUpType(upType);
					problem.setProblemType(typeMap.get(upType+key6));
					problem.setLabelId(labelMap.get(key5));
					problem.setHearingId(hcId);
					problemList.add(problem);
					if(!StrUtile.isEmpty(key8)){
						EnProblemAnswer problemAnswer = new EnProblemAnswer();
						problemAnswer.setProblemAnswerLong(key8);
						problemAnswer.setProblemId(problem.getProblemId());
						problemAnswerList.add(problemAnswer);
					}
					// 选项
					for (int j = 10;j<rowArray.size() || j< 24;j++){
						String key = XlsUtil.getValue(rowArray, j);
						if(StrUtile.isEmpty(key)){
							break;
						}
						EnProblemOption option = new EnProblemOption();
						option.setOptionId(StrUtile.getId());
						option.setOptionTitle(key);
						option.setOptionTrue("2");
						option.setProblemId(problem.getProblemId());
						optionList.add(option);
					}
				}
			}else if("5,6".contains(upType)){ // 翻译题 和 写作题
				// 如果是新开始的 Section 则重新建立一篇文章
				if(!StrUtile.isEmpty(key2) || !StrUtile.isEmpty(key3)){
					EnParagraph enParagraph = new EnParagraph();
					hcId = StrUtile.getId();
					enParagraph.setParagraphId(hcId);
					enParagraph.setParagraphSection(hc[1]);
					enParagraph.setParagraphDirections(hc[2]);
					enParagraph.setParagraphTitle(key3);
					if(!StrUtile.isEmpty(key4)){
						enParagraph.setParagraphTitleTs(key4);
					}
					enParagraph.setProblemUpType(upType);
					enParagraph.setProblemDate(problemDate);
					enParagraph.setCtime(new Date());
					enParagraph.setLabelId(labelMap.get(key5));
					paragraphList.add(enParagraph);
					EnProblem problem = new EnProblem();
					problem.setProblemId(StrUtile.getId());
					problem.setProblemUpType(upType);
					problem.setProblemType(typeMap.get(upType+key6));
					problem.setLabelId(labelMap.get(key5));
					problem.setParagraphId(hcId);
					problemList.add(problem);
					if(!StrUtile.isEmpty(key8)){
						EnProblemAnswer problemAnswer = new EnProblemAnswer();
						problemAnswer.setProblemAnswerLong(key8);
						problemAnswer.setProblemId(problem.getProblemId());
						problemAnswerList.add(problemAnswer);
					}
					// 选项
					for (int j = 10;j<rowArray.size() || j< 24;j++){
						String key = XlsUtil.getValue(rowArray, j);
						if(StrUtile.isEmpty(key)){
							break;
						}
						EnProblemOption option = new EnProblemOption();
						option.setOptionId(StrUtile.getId());
						option.setOptionTitle(key);
						option.setOptionTrue("2");
						option.setProblemId(problem.getProblemId());
						optionList.add(option);
					}
				}
			}

			if("词汇题".equals(key6)){
				upType = "2";// 词汇题归拢到阅读题下边
			}
			if(!typeMap.containsKey(upType+key6)){
				msg.add(new XlsMsgBean("试题页第" + (i + 1) + "行第G列", "题目分类填写错误!"));
			}
			if(StrUtile.isEmpty(key7) || StrUtile.isEmpty(key5)){
				continue;
			}

			EnProblem problem = new EnProblem();
			problem.setProblemId(StrUtile.getId());
			problem.setProblemUpType(upType);
			problem.setProblemType(typeMap.get(upType+key6));
			problem.setLabelId(labelMap.get(key5));
			problem.setProblemTitle(key7);
			if("2".equals(upType)){
				problem.setParagraphId(hcId);
			}
			if("词汇题".equals(key6)){
				problem.setProblemUpType("2");// 词汇题归拢到阅读题下边
				problem.setParagraphId(hcId);
				problem.setWordBank(key7);
			}
			if("4".equals(upType)){
				problem.setHearingId(hcId);
			}
			if(!StrUtile.isEmpty(key9)){
				EnProblemAnswer problemAnswer = new EnProblemAnswer();
				problemAnswer.setProblemAnswerLong(key9);
				problemAnswer.setProblemId(problem.getProblemId());
				problemAnswerList.add(problemAnswer);
			}

			// 选项
			int s = 0;
			for (int j = 10;j<rowArray.size() || j< 50;j++){
				String key = XlsUtil.getValue(rowArray, j);
				if(StrUtile.isEmpty(key)){
					break;
				}
				EnProblemOption option = new EnProblemOption();
				option.setOptionId(StrUtile.getId());
				option.setOptionTitle(key);
				option.setOptionTrue("2");
				option.setProblemId(problem.getProblemId());
				if(an[j-10].equals(key8)){
					problem.setProblemAnswer(key);
					option.setOptionTrue("1");
				}
				optionList.add(option);
				s++;
			}
			if("2".equals(problem.getProblemUpType())) {
				if (StrUtile.isEmpty(problem.getProblemAnswer())) {
					msg.add(new XlsMsgBean("试题页第" + (i + 1) + "行第J列", "问题答案解析失败!"));
				}
				if (s < 4) {
					msg.add(new XlsMsgBean("试题页第" + (i + 1) + "行", "问题候选项少于4个,请检查!"));
				}
			}
			problemList.add(problem);
		}
		if(paragraphList.stream().anyMatch(p -> "2".equals(p.getProblemUpType())) && wordBankList.isEmpty()){
			msg.add(new XlsMsgBean("试题页", "文章下未找到匹配的词汇题!"));
		}
		if(msg.isEmpty()){
			if(!problemList.isEmpty()){
				enProblemMapper.insertAllEnProblem(problemList,problemDate);
			}
			if(!optionList.isEmpty()){
				enProblemOptionMapper.insertAllEnProblemOption(optionList);
			}
			if(!problemAnswerList.isEmpty()){
				enProblemAnswerMapper.insertAllEnProblemAnswer(problemAnswerList);
			}
			if(!paragraphList.isEmpty()){
				enParagraphMapper.insertAllEnParagraph(paragraphList);
			}
			if(!hearingList.isEmpty()){
				enHearingMapper.insertAllEnHearing(hearingList);
			}
			if(!wordBankList.isEmpty()){
				enWordBankMapper.insertAllEnWordBank(wordBankList);
			}
		}
		JSONObject rut = new JSONObject();
		rut.put("list", msg);
		return rut;
	}

	/**
	 * 解析听力题
	 */
	private static void Hearing6(JSONArray sheetArray,String difficulty,
								 Map<String,String> labelMap,Map<String,String> typeMap,
								 List<EnHearing> hearingList,List<EnProblem> problemList,
								 List<EnProblemOption> optionList,JSONArray msg,String problemDate
	) {
		String dd = "1".equals(difficulty)?"听力四级":"听力六级";
		// 答案排序
		String[] an = {"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O"};
		// 缓存字段 题型 Section 题目说明 阅读文章/听力文字 文章译文 题目板块
		String[] hc = {"","","","","","","","",""};
		// 缓存主键 文章标识
		String hcId = "";
		String upType = "1";
		StringBuilder tx = new StringBuilder();
		for (int i = 1; i < sheetArray.size(); i++) {
			JSONArray rowArray = sheetArray.getJSONArray(i);
			// Section
			String key1 = XlsUtil.getValue(rowArray, 1);
			if(!StrUtile.isEmpty(key1)){
				hc[1] = key1;
			}
			// 题目说明
			String key2 = XlsUtil.getValue(rowArray, 2);
			if(!StrUtile.isEmpty(key2)){
				hc[2] = key2;
			}
			// 是否对话
			String key3 = XlsUtil.getValue(rowArray, 3);
			if(!StrUtile.isEmpty(key3)){
				if(!StrUtile.isEmpty(tx.toString()) && !StrUtile.isEmpty(hc[3]) && (!tx.toString().contains("推断题") || !tx.toString().contains("事实题"))){
					msg.add(new XlsMsgBean(dd+"页第" + (i) + "行", hc[1]+"题型缺失,请检查!"));
				}
				if(hc[1].contains("A") && "2".equals(difficulty) && !"是".equals(key3)){
					msg.add(new XlsMsgBean(dd+"页第" + (i + 1) + "行第D列", "Section A不是对话,请检查!"));
				}
				hc[3] = key3;
				tx = new StringBuilder();
			}
			// 语音
			String key4 = XlsUtil.getValue(rowArray, 4);
			if(!StrUtile.isEmpty(key4)){
				if(!"咨询女声,咨询女声38,咨询女声39,教育女声,教育女声38,教育女声39,客服男声,客服男声37,客服男声38".contains(key4)){
					msg.add(new XlsMsgBean(dd+"页第" + (i + 1) + "行第E列", "播音人错误,请检查!"));
				}
				hc[4] = key4;
			}
			// 阅读文章/听力文字
			String key5 = XlsUtil.getValue(rowArray, 5);
			if(!StrUtile.isEmpty(key5)){
				if(hc[1].contains("A") && "1".equals(difficulty) && !key5.contains("News Report")){
					msg.add(new XlsMsgBean(dd+"页第" + (i + 1) + "行第F列", "Section A文字脚本不包含News Report关键字,请检查!"));
				}
				hc[5] = key5;
			}
			// 题目板块
			String key6 = XlsUtil.getValue(rowArray, 6);
			if(!StrUtile.isEmpty(key6)){
				hc[6] = key6;
			}
			// 小分类
			String key7 = XlsUtil.getValue(rowArray, 7);
			// 题干
			String key8 = XlsUtil.getValue(rowArray, 8);
			// 答案
			String key9 = XlsUtil.getValue(rowArray, 9);



			// 是否对话标识 表示为一篇文章
			if("是".equals(key3)){
				hcId = StrUtile.getId();
			}
			if(!StrUtile.isEmpty(key5)){
				if("否".equals(hc[3])){ // 如果不是对话 则一个单元格一篇文章
					hcId = StrUtile.getId();
					EnHearing enHearing = new EnHearing();
					enHearing.setHearingId(StrUtile.getId());
					enHearing.setUpHearingId(hcId);
					enHearing.setHearingSection(hc[1]);
					enHearing.setHearingDirections(hc[2]);
					enHearing.setHearingTitle(key5);
					enHearing.setHearingType("1");
					enHearing.setProblemUpType("1");
					enHearing.setAnnouncer(hc[4]);
					enHearing.setHearingDialogue("2");
					enHearing.setProblemDate(problemDate);
					enHearing.setCtime(new Date());
					enHearing.setLabelId(labelMap.get(hc[6]));
					hearingList.add(enHearing);
				}
				if("是".equals(hc[3])){
					String[] key5s = key5.split("\n");
					for (String kk5:key5s) {
						List<String> ss = SpliteText.sq(kk5);
						if(ss.size() < 5){
							ss = Arrays.asList(kk5);
						}
						for (String k5 : ss) {
							EnHearing enHearing = new EnHearing();
							enHearing.setHearingId(StrUtile.getId());
							enHearing.setUpHearingId(hcId);
							enHearing.setHearingSection(hc[1]);
							enHearing.setHearingDirections(hc[2]);
							enHearing.setHearingTitle(k5.trim());
							enHearing.setHearingType("1");
							enHearing.setProblemUpType("1");
							enHearing.setAnnouncer(hc[4]);
							enHearing.setHearingDialogue("1");
							enHearing.setProblemDate(problemDate);
							enHearing.setCtime(new Date());
							enHearing.setLabelId(labelMap.get(hc[6]));
							hearingList.add(enHearing);
						}
					}
				}
			}
			if(StrUtile.isEmpty(key8) || StrUtile.isEmpty(key6)){
				if(!"是".equals(hc[3])){
					msg.add(new XlsMsgBean(dd+"页第" + (i + 1) + "行", "文章没有关联题目!"));
				}
				continue;
			}
			if(!labelMap.containsKey(key6)){
				msg.add(new XlsMsgBean(dd+"页第" + (i + 1) + "行第H列", "题目所属板块填写错误!"));
			}
			if(!typeMap.containsKey(upType+key7)){
				msg.add(new XlsMsgBean(dd+"页第" + (i + 1) + "行第I列", "题目分类填写错误!"));
			}
			if(StrUtile.isEmpty(key9)){
				msg.add(new XlsMsgBean(dd+"页第" + (i + 1) + "行第J列", "问题答案为空!"));
			}


			EnProblem problem = new EnProblem();
			problem.setProblemId(StrUtile.getId());
			problem.setProblemUpType(upType);
			problem.setProblemType(typeMap.get(upType+key7));
			problem.setLabelId(labelMap.get(key6));
			problem.setProblemTitle(key8);
			problem.setHearingId(hcId);
			problem.setProblemDifficulty(difficulty);

			tx.append(key7);
			// 选项
			int s = 0;
			for (int j = 10;j<rowArray.size() || j< 15;j++){
				String key = XlsUtil.getValue(rowArray, j);
				if(StrUtile.isEmpty(key)){
					break;
				}
				EnProblemOption option = new EnProblemOption();
				option.setOptionId(StrUtile.getId());
				option.setOptionTitle(key);
				option.setOptionTrue("2");
				option.setProblemId(problem.getProblemId());
				if(an[j-10].equals(key9)){
					problem.setProblemAnswer(key);
					option.setOptionTrue("1");
				}
				optionList.add(option);
				s++;
			}
			if(StrUtile.isEmpty(problem.getProblemAnswer())){
				msg.add(new XlsMsgBean(dd+"页第" + (i + 1) + "行第J列", "问题答案解析失败!"));
			}
			if(s < 4){
				msg.add(new XlsMsgBean(dd+"页第" + (i + 1) + "行", "问题候选项少于4个,请检查!"));
			}

			problemList.add(problem);
		}
	}
}